16. LEFT and RIGHT JOIN

Select all of the below that are true.

A LEFT JOIN and RIGHT JOIN do the same thing if we change the tables that are in the FROM and JOIN statements.

A LEFT JOIN will at least return all the rows that are in an INNER JOIN.

JOIN and INNER JOIN are the same.

A LEFT OUTER JOIN is the same as LEFT JOIN.

SOLUTION:

• A **LEFT JOIN** and **RIGHT JOIN** do the same thing if we change the tables that are in the **FROM** and **JOIN** statements.
• A **LEFT JOIN** will **at least** return all the rows that are in an **INNER JOIN**.
• **JOIN** and **INNER JOIN** are the same.
• A **LEFT OUTER JOIN** is the same as **LEFT JOIN**.

Above are two small tables for you to test your knowledge of JOINs. You can click on the image to get a better view.

Country has 6 rows and 2 columns:

• countryid and countryName

State has 6 rows and 3 columns:

• stateid, countryid, and stateName

  Use the above tables to determine the solution to the following questions.

QUIZ QUESTION::

Match each statement to the item it describes.

ANSWER CHOICES:

Country.countryName

Country.countryid

State.stateid

State.stateName

State.countryid

Description	Item
The primary key of the Country table.
The primary key of the State table.
The foreign key that would be used in JOINing the tables.

SOLUTION:

Description	Item
The primary key of the Country table.	Country.countryid
The primary key of the State table.	State.stateid
The foreign key that would be used in JOINing the tables.	State.countryid

The above two tables are given again just for minimizing scrolling. If you were to perform the following query:

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
JOIN State s
ON c.countryid = s.countryid;

QUIZ QUESTION::

Match the results of the query to the description.

ANSWER CHOICES:

7

0

3

12

5

8

2

4

1

6

Description	Result
The number of columns in resulting table.
The number of rows in the resulting table.
The number of times countryid 1 will show up in resulting table.
The number of times countryid 6 will show up in the resulting table.

SOLUTION:

Description	Result
The number of times countryid 6 will show up in the resulting table.	0
The number of columns in resulting table.	3
The number of times countryid 1 will show up in resulting table.	2
The number of rows in the resulting table.	6

The above two tables are given again just for minimizing scrolling. If you were to perform the following query:

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
LEFT JOIN State s
ON c.countryid = s.countryid;

QUIZ QUESTION::

Match the results of the query to the description.

ANSWER CHOICES:

8

0

7

9

2

5

4

1

6

10

3

Description	Results
The number of columns in resulting table.
The number of rows in the resulting table.
The number of times countryid 1 will show up in resulting table.
The number of times countryid 6 will show up in the resulting table.

SOLUTION:

Description	Results
The number of rows in the resulting table.	8
The number of times countryid 1 will show up in resulting table.	2
The number of times countryid 6 will show up in the resulting table.	1
The number of columns in resulting table.	3